
//定义状态常量 1为正常 0为删除
const STATUS = 1;

module.exports ={
    
    // 用户登录
    queryPasswordByName : 'SELECT' +
        ' l.user_id,l.login_name,l.password,t.type_name as login_type' +
    ' FROM' +
        ' login as l,' +
        ' login_type as t' +
    ' WHERE' +
        ' l.login_type= t.id' +
    ' AND' +
        ' login_name = ?'+
    ' AND'+
        ' status = '+STATUS+';',
    
    // 查询用户信息
	queryUserInfoById : 'SELECT' +
        ' id as user_id,' +
        'head_portrait,nick_name,' +
        'gender,birthday,signature,address' +
    ' FROM' +
        ' user' +
    ' WHERE' +
        ' id= ?',
    
    // 检查用户信息
	checkedRegisterInfo: 'SELECT' +
        ' l.login_name,u.nick_name' +
    ' FROM' +
        ' login as l ' +
    ' JOIN ' +
        ' user as u ' +
    ' ON ' +
        ' l.user_id = u.id AND l.status = '+STATUS+'' +
    ' WHERE' +
        ' l.login_name = ?' +
    ' OR' +
        ' u.nick_name = ?;',
    
    // 增加用户
    addUser: 'INSERT INTO' +
        ' user(nick_name)' +
    ' VALUE(?)',
    
    // 增加登录
    addLogin: 'INSERT INTO' +
        ' login(login_name,password,status,login_type,user_id)' +
    ' VALUE(?,?,?,?,?)',
    
    // 更正密码
	updateLoginPassword: 'UPDATE' +
        ' login' +
    ' SET' +
        ' password = ?' +
    ' WHERE' +
        ' login_name = ?',
    
    // 更新用户的信息
    updateUserInfoById: 'UPDATE' +
        ' user' +
    ' SET' +
        ' head_portrait= ?,nick_name= ?,gender= ?,birthday= ?,address= ?,signature= ?' +
    ' WHERE' +
        ' id = ?;',
    
    // 查询粉丝用户或者关注用户
	querySuperiorUserAndFansUser: 'SELECT' +
        ' u.*,a.superior_id,a.fans_id' +
    ' FROM' +
        ' user as u,attention_relation as a' +
    ' WHERE' +
        ' u.id = a.fans_id AND a.superior_id= ?' +
    ' OR' +
        ' u.id = a.superior_id AND a.fans_id= ?;',
    
    // 先删除
    deleteAttentionRelation: 'DELETE FROM' +
        ' attention_relation' +
    ' WHERE' +
    '    superior_id= ? AND fans_id= ?',
    
	// 对粉丝增加关注
	addSuperiorByFansId: 'INSERT INTO' +
        ' attention_relation(superior_id, fans_id)' +
    ' VALUE (?,?);',
};